USE [abyx_svcm]
GO


ALTER VIEW [dbo].[COMP_SODetails]
AS
	SELECT 
		SO.[ServiceOrderId]
		,SO.[CustomerId]
		,SO.[OrderNumber]
		,SO.[OrderReceivedDate]
		,SO.[OrderCompletedDate]
		,SO.[ServiceEquipmentId]
		,SO.[ServiceItemImieSerialNumber]
		,SO.[EstimatedDate]
		,SO.[EstimatedPrice]
		,SO.[RegistrationFee]
		,SO.[ActualPrice]
		,SO.[EstimateApproved]
		,SO.[Status]
		,SO.[LMUserId]
		,SO.[LMDate]
		,C.CustomerNo 
		,C.IdNo 
		,C.Name 
		,C.Address 
		,C.Mob 
		,q.ProductModelNumber 
		,q.ManufacturerName
		,q.Name AS EquipmentName
		,(SELECT REPLACE(Value, SUBSTRING(Value, CHARINDEX('<', Value), CHARINDEX('>', Value) - CHARINDEX('<', Value) + 1),'') AS Value
			FROM (
					SELECT 
						REPLACE(Value, SUBSTRING(Value, CHARINDEX('<', Value), CHARINDEX('>', Value) - CHARINDEX('<', Value) + 1),'') AS Value
					FROM (
							SELECT 
								(SELECT 
									 (st.Observation + CHAR(10) + 
									st.FaultDescription) AS details 
								FROM 
									[SVC_SOEquipmentServiceTasks] AS sot  
									JOIN [SVC_ServiceTasks] AS st ON st.ServiceTaskId = sot.ServiceTaskId 
								FOR XML PATH('')) AS Value
							FROM SVC_SOEquipmentServiceTasks
							WHERE [ServiceOrderId]=SO.ServiceOrderId) AS t1) AS t2) AS details
		,((SELECT REPLACE(Value, SUBSTRING(Value, CHARINDEX('<', Value), CHARINDEX('>', Value) - CHARINDEX('<', Value) + 1),'') AS Value
			FROM (
					SELECT 
						REPLACE(Value, SUBSTRING(Value, CHARINDEX('<', Value), CHARINDEX('>', Value) - CHARINDEX('<', Value) + 1),'') AS Value
					FROM (
							SELECT 
								(SELECT 
									 (st.Observation + CHAR(10) + 
									st.FaultDescription) AS details 
								FROM 
									[SVC_SOEquipmentServiceTasks] AS sot  
									JOIN [SVC_ServiceTasks] AS st ON st.ServiceTaskId = sot.ServiceTaskId 
								FOR XML PATH('')) AS Value
							FROM SVC_SOEquipmentServiceTasks
							WHERE [ServiceOrderId]=SO.ServiceOrderId) AS t1) AS t2) +
			ISNULL(SO.[OrderNumber],'') +
			ISNULL(C.CustomerNo,'') +
			ISNULL(C.IdNo,'') + 
			ISNULL(C.Name,'') + 
			ISNULL(C.Address,'') + 
			ISNULL(C.Mob,'')) AS SearchField
	FROM 
		[SVC_ServiceOrders] AS SO
		LEFT OUTER JOIN SVC_Customers AS C ON C.CustomerId = SO.CustomerId 
		LEFT OUTER JOIN SQC_Catalog AS q ON q.EquipmentId = SO.ServiceEquipmentId 
GO


